package com.ruoyi.accountant.dao;

import com.ruoyi.accountant.domain.Reason;
import com.ruoyi.accountant.domain.other.ProfitLossReport;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

//@Repository
public class ReportDao {
//    @PersistenceContext
    private EntityManager entityManager;

//    @Autowired
    private ReasonDao reasonDao;

    public List<List<ProfitLossReport>> findMonthMoneyCountByTypeAndReason(String type, Long ofReason) {
        //TODO 此处因工期有限后续需要完善
        List<Reason> allByParent = reasonDao.findAllByParent(ofReason);

        List<List<ProfitLossReport>> list = new ArrayList<List<ProfitLossReport>>();

        for (Reason reason : allByParent) {

            String sql = "select t.month, t.money,tr.rname,t.tax from (select month(proof_date) as month, sum(main_currency_money) as money, of_reason, sum(tax) as tax\n" +
                    "    from t_transaction_detail as t where of_reason = ? and deleted = 0 group by of_reason, month(proof_date)) as t\n" +
                    "        left outer join t_reason tr on tr.rid = t.of_reason where tr.rtype=?;";

            Query nativeQuery = entityManager.createNativeQuery(sql);
            nativeQuery.setParameter(1, reason.getRid());
            nativeQuery.setParameter(2, type);
            List<Object[]> resultList = nativeQuery.getResultList();
            List<ProfitLossReport> profitLossReportList = new ArrayList<ProfitLossReport>(resultList.size());

            for (Object[] o : resultList) {
                profitLossReportList.add(new ProfitLossReport(((Integer) o[0]).intValue(), (BigDecimal) o[1], (String)o[2], (BigDecimal) o[3]));
            }

            list.add(profitLossReportList);
        }

        return list;
    }

    /***
     * 查询应收款和应付款的额度
     * @param ofReasoon
     * @param dtype
     * @return
     */
    public List<Object[]> findSumMoneyByReason(Long ofReasoon, String dtype) {
        String sql = "select sum(t.money) as money,rname from t_transaction_detail as t\n" +
                "    left outer join t_reason as r on t.of_reason = r.rid\n" +
                "where of_reason = ? and dtype = ? and t.deleted = 0";

        Query nativeQuery = entityManager.createNativeQuery(sql);
        nativeQuery.setParameter(1, ofReasoon);
        nativeQuery.setParameter(2, dtype);
        return nativeQuery.getResultList();
    }

    public List<Object[]> findSumMoneyByInaccount(Long ofAccount) {
        String sql = "select money, c.cn_name from (select sum(outaccount_money) as money,inaccount_currency\n" +
                "    from t_inaccount where inaccount = ? group by inaccount_currency) as t left outer join t_currency c on t.inaccount_currency = c.cid;";

        Query nativeQuery = entityManager.createNativeQuery(sql);
        nativeQuery.setParameter(1, ofAccount);
        return nativeQuery.getResultList();
    }

    public List<Object[]> findSumMoneyByOutaccount(Long ofAccount) {
        String sql = "select money, c.cn_name from (select sum(outaccount_money) as money,outaccount_currency\n" +
                "    from t_inaccount where outaccount = ? group by outaccount_currency) as t left outer join t_currency c on t.outaccount_currency = c.cid;";

        Query nativeQuery = entityManager.createNativeQuery(sql);
        nativeQuery.setParameter(1, ofAccount);
        return nativeQuery.getResultList();
    }

    public List<Object[]> findCashFlowByType(String type, Date start, Date end) {
        String sql = "select money, cn_name,of_account,a.aname  from\n" +
                "    (select sum(money) as money, of_currency,of_account from t_transaction_detail\n" +
                "        where dtype = ? and deleted = 0 and proof_date between ? and ?\n" +
                "            group by of_currency) as t\n" +
                "    left outer join t_currency as c on t.of_currency = c.cid\n" +
                "    left outer join t_account as a on t.of_account = a.aid;";

        Query nativeQuery = entityManager.createNativeQuery(sql);
        nativeQuery.setParameter(1, type);
        nativeQuery.setParameter(2, start);
        nativeQuery.setParameter(3, end);

        return nativeQuery.getResultList();
    }
}
